If you run reports from the SEOmoz toolset, you may find it difficult to make your data look as sexy as the graphs you see on the site. I hope to close that gap with this post and give you the tools to format your data like a pro. To accomplish this objective, I’ve provided several resources:
- A video walkthrough of how to format your data like one of the SEOmoz charts for you DIY’ers out there.
- Chart template files for all of the charts on the SEOmoz site that have export options (there are three in total).
- Instructions on how to load the template files.
- A video walkthrough of the data prep and cleanup steps required with the templates.
Here’s a screenshot of the three charts you’ll be able to replicate:
Download the Excel file
Editor’s note: These downloads are no longer available.
You can download the Excel file I worked from to create these charts to follow along. Whether you watch the video to learn how to steal all of SEOmoz’s formatting ideas format like an SEOmoz data scientist or take the shortcut and download the chart template files, you may want to check out the Excel file for tips.
Watch over my shoulder as I create one
The report I’ll be demonstrating is the Historical Domain Analysis chart, found under Pro Dashboard (or Campaigns) > [choose your campaign] > Link Analysis > History. Here’s a before/after picture of what I show you how to do:
I could write out all of the steps to replicate the Historical Domain Analysis chart, but most people find it easier to watch over someone’s shoulder. Plus, it takes a fraction of the time to watch the steps as opposed to reading and interpreting all of the screenshots. I go through the steps on my Mac, but provide the steps for PC each step of the way. They really are variations on a theme.
Load chart template files
I went through all the SEOmoz reports that a) offer an export, and b) include a graph of some type on the site and created templates, so you don’t have to format the data yourselves. It’s pretty easy to use them. However, some of the raw data needs to be massaged a bit before you can apply the templates.
Editor’s note: The following download is no longer available.
To load them on your computer, unzip and drop these template files in the chart templates folder on your hard drive.
- PC: C:/Users/[Your User Name]/AppData/Roaming/Microsoft/Templates/Charts
- Mac: Users/[Your User Name]/Library/Application Support/Microsoft/Office/Chart Templates
Note: If your Charts directory isn’t in the default location and you’re on a PC, you can click the Manage Templates button by navigating to Insert > Charts > Other Charts > All Chart Types.
Data prep and chart cleanup
Each report has its own steps that you have to take to prepare the export for charting and a few cleanup steps you have to take afterwards. I don’t normally do two videos in one post because I don’t have a death wish; however, after writing out all the steps and taking the screenshots, I realized it looked scarier than it is. So I fired off another video to hopefully allay some of the intimidation.
Make your own
If you want to modify a chart(s) to your liking, no worries. It won’t hurt my feelings. 🙂 When you’re finished with your changes, you can save it as your own template. On a PC, you would just choose Chart Tools > Design > Save As Template. On a Mac, the easiest way is to right-click on the chart and choose Save as Template. But if you prefer using the Ribbon, go to Charts > Change Chart Type > Other > Save as Template. (Like I said, use the contextual menu.)
More resources
Here are links to the two posts I referenced in the second video for a deeper explanation:
Easy To Advanced Uses Of Cell Formatting In Excel
How To Make A Two-Sided Bar Chart [VIDEO]
If you need help
So, if you want to regale your boss, client, or mom by replicating SEOmoz’s charts, hopefully this will give you the tools you need to do that. If you’ve picked up a few data visualization tips in the process, even better. But if you get stuck along the way or run into bugs I didn’t run into (I didn’t test these on older versions of Excel), feel free to comment below or hit me up on Twitter. Thanks for reading!
